#!/bin/bash
# name: exp_table.sh

# Custom log 
LOGFILE="../log/exp_table.log"
:>>"$LOGFILE"
exec 1>>"$LOGFILE"
exec 2>&1

echo "`date "+%F %T"` START #############################################################################"
. ~/.bash_profile
export NLS_LANG=AMERICAN_AMERICA.UTF8

# Custom variable
DATE1=`date -d '-9 day' +%F`
DATE2=`date -d yesterday +%F`
FILEY=`date +%Y%m%d`

# Create folder
mkdir ../data/$FILEY

# README
# R_DETAIL T_SRVAPPRAISE T_CC_CALL_NUM_4 ELECTRONIC_VOUCHER_DETAIL
sqlplus -s / as sysdba <<EOF
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
spool ../data/$FILEY/ENT_RECORD_BX_TABLE_$FILEY.txt
select 'SESSION_ID' || '|' || 'REGION_ID' || '|' || 'CALLTYPE' || '|' ||
       'AGENT_ID' || '|' || 'AGENT_NAME' || '|' || 'AGENT_DN' || '|' ||
       'START_TIME' || '|' || 'END_TIME' || '|' || 'LOCAL_URL' || '|' ||
       'REMOTE_URL' || '|' || 'SKILL_ID' || '|' || 'SKILL_DESC' || '|' ||
       'IVR_DURATION' || '|' || 'QUEUE_DURATION' || '|' || 'ALERT_DURATION' || '|' ||
       'TALK_DURATION' || '|' || 'ACW_DURATION' || '|' || 'END_TYPE'
  from dual
union all
select SESSION_ID || '|' || REGION_ID || '|' || CALLTYPE || '|' || AGENT_ID || '|' ||
       AGENT_NAME || '|' || AGENT_DN || '|' || START_TIME || '|' ||
       END_TIME || '|' || LOCAL_URL || '|' || REMOTE_URL || '|' || SKILL_ID || '|' ||
       SKILL_DESC || '|' || IVR_DURATION || '|' || QUEUE_DURATION || '|' ||
       ALERT_DURATION || '|' || TALK_DURATION || '|' || ACW_DURATION || '|' ||
       END_TYPE
  from "0101100048".R_DETAIL
 where START_TIME > to_date('$DATE1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and START_TIME < to_date('$DATE2 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
spool off

spool ../data/$FILEY/T_SRVAPPRAISE_$FILEY.txt
select 'SA_SEID' || '|' || 'SA_ID' || '|' || 'SA_ANI' || '|' ||
       'SA_DNIS' || '|' || 'SA_AGENTID' || '|' || 'SA_AGENTDN' || '|' ||
       'SA_SCORE' || '|' || 'SA_TIME' || '|' || 'SA_AGENTSTARTTIME' || '|' ||
       'SA_AGENTENDTIME' || '|' || 'SA_RESERVED1' || '|' || 'SA_RESERVED2'
  from dual
union all
select SA_SEID || '|' || SA_ID || '|' || SA_ANI || '|' || SA_DNIS || '|' ||
       SA_AGENTID || '|' || SA_AGENTDN || '|' || SA_SCORE || '|' ||
       SA_TIME || '|' || SA_AGENTSTARTTIME || '|' || SA_AGENTENDTIME || '|' || 
	   SA_RESERVED1 || '|' || SA_RESERVED2
  from "0101100048".T_SRVAPPRAISE
 where SA_TIME > to_date('$DATE1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and SA_TIME < to_date('$DATE2 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
spool off

spool ../data/$FILEY/T_CC_CALL_NUM_4_$FILEY.txt
select 'OBJ_ID' || '|' || 'START_TIME' || '|' || 'SKILL_ID' || '|' ||
       'AREA_ID' || '|' || 'REGION_ID' || '|' || 'CN_TL_IB_TRN_AG_AN' || '|' ||
       'CT_TL_IB_QU' || '|' || 'CN_TL_IB_QU' || '|' || 'CT_TL_IB_AG_RN' || '|' ||
       'CN_TL_IB_AG_RN' || '|' || 'CN_TL_IB_AG_AN_I15' || '|' || 'CN_TL_IB_AG_AB_I10' || '|' ||
       'CT_TL_IB_AG_TK' || '|' || 'CN_TL_IB_IVR' || '|' || 'CN_TL_IB_TRN_AG' || '|' ||
       'CN_TL_IB_TRN_AG_AB' || '|' || 'CT_TL_IB_AG_AB' || '|' || 'CT_TL_IB_AG_ACW' || '|' || 
	   'CT_AG_ONLINE' || '|' || 'CT_TL_IB_AG_AN' || '|' || 'CN_TL_IB_AG_ACW' || '|' || 
	   'CT_AG_READY' || '|' || 'CN_TL_IB' || '|' || 'CN_TL_AG_IB'
  from dual
union all
select OBJ_ID || '|' || START_TIME || '|' || SKILL_ID || '|' || AREA_ID || '|' ||
       REGION_ID || '|' || CN_TL_IB_TRN_AG_AN || '|' || CT_TL_IB_QU || '|' ||
       CN_TL_IB_QU || '|' || CT_TL_IB_AG_RN || '|' || CN_TL_IB_AG_RN || '|' || CN_TL_IB_AG_AN_I15 || '|' ||
       CN_TL_IB_AG_AB_I10 || '|' || CT_TL_IB_AG_TK || '|' || CN_TL_IB_IVR || '|' ||
       CN_TL_IB_TRN_AG || '|' || CN_TL_IB_TRN_AG_AB || '|' || CT_TL_IB_AG_AB || '|' ||
       CT_TL_IB_AG_ACW || '|' || CT_AG_ONLINE || '|' || CT_TL_IB_AG_AN || '|' || 
	   CN_TL_IB_AG_ACW || '|' || CT_AG_READY || '|' || CN_TL_IB || '|' || CN_TL_AG_IB
  from "0101100048".T_CC_CALL_NUM_4
 where START_TIME >= to_date('$DATE1', 'yyyy-mm-dd')
   and START_TIME < to_date('$DATE2', 'yyyy-mm-dd');
spool off

spool ../data/$FILEY/ELECTRONIC_VOUCHER_DETAIL_$FILEY.txt
select 'START_TIME' || '|' || 'CUSTOME_PHONE' || '|' || 'IS_EXIST' || '|' ||
       'IS_LOCK' || '|' || 'IS_UNLOCK' || '|' || 'IS_SEND_SUCCESS' || '|' ||
       'IS_SEND_UNLOCK_SUCCESS'
  from dual
union all
select DISTINCT START_TIME || '|' || CUSTOME_PHONE || '|' || IS_EXIST || '|' || IS_LOCK || '|' ||
       IS_UNLOCK || '|' || IS_SEND_SUCCESS || '|' || IS_SEND_UNLOCK_SUCCESS
  from "0101100048".ELECTRONIC_VOUCHER_DETAIL
 where START_TIME > to_date('$DATE1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and START_TIME < to_date('$DATE2 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
spool off
exit
EOF
sleep 3

# Create file done
touch ../data/$FILEY/R_DETAIL_$FILEY.txt.done
touch ../data/$FILEY/T_SRVAPPRAISE_$FILEY.txt.done
touch ../data/$FILEY/T_CC_CALL_NUM_4_$FILEY.txt.done
touch ../data/$FILEY/ELECTRONIC_VOUCHER_DETAIL_$FILEY.txt.done


# upload sftp exp_table
/usr/bin/expect <<EOF
spawn sftp -oPort=22 cs_channelsoft@114.247.39.119:/cs_channelsoft
sleep 3
expect "password:" { send "RR|gxjd62\r" }
sleep 2
expect "sftp>" { send "put ../data/$FILEY/R_DETAIL_$FILEY.txt\r" }
sleep 5
expect "sftp>" { send "put ../data/$FILEY/R_DETAIL_$FILEY.txt.done\r" }
sleep 1
expect "sftp>" { send "put ../data/$FILEY/T_SRVAPPRAISE_$FILEY.txt\r" }
sleep 5
expect "sftp>" { send "put ../data/$FILEY/T_SRVAPPRAISE_$FILEY.txt.done\r" }
sleep 1
expect "sftp>" { send "put ../data/$FILEY/T_CC_CALL_NUM_4_$FILEY.txt\r" }
sleep 5
expect "sftp>" { send "put ../data/$FILEY/T_CC_CALL_NUM_4_$FILEY.txt.done\r" }
sleep 1
expect "sftp>" { send "put ../data/$FILEY/ELECTRONIC_VOUCHER_DETAIL_$FILEY.txt\r" }
sleep 5
expect "sftp>" { send "put ../data/$FILEY/ELECTRONIC_VOUCHER_DETAIL_$FILEY.txt.done\r" }
sleep 1
expect "sftp>" { send "exit\r" }
interact
EOF

echo "`date "+%F %T"` END ###############################################################################"
echo ""
echo ""
